from sqlalchemy.orm import Session
from sqlalchemy import and_, or_
from sqlalchemy.orm import Session
from typing import List, Optional, Dict, Any
from datetime import datetime

from db_models import TodoDB, UserDB
from models import TodoStatus, TodoPriority, TodoCreate, TodoUpdate, Todo

class TodoService:
    """
    待办事项数据库服务层
    封装所有数据库操作，提供业务逻辑接口
    """
    
    @staticmethod
    def get_all_todos(
        db: Session, 
        user_id: int,
        status: Optional[TodoStatus] = None,
        priority: Optional[TodoPriority] = None,
        limit: Optional[int] = None
    ) -> List[TodoDB]:
        """
        获取用户的所有待办事项，支持过滤和限制数量
        """
        query = db.query(TodoDB).filter(TodoDB.user_id == user_id)
        
        # 应用过滤条件
        if status:
            query = query.filter(TodoDB.status == status)
        if priority:
            query = query.filter(TodoDB.priority == priority)
        
        # 按 order 字段排序
        query = query.order_by(TodoDB.order.asc(), TodoDB.created_at.asc())
        
        # 限制数量
        if limit and limit > 0:
            query = query.limit(limit)
        
        return query.all()
    
    @staticmethod
    def get_todo_by_id(db: Session, todo_id: int, user_id: int) -> Optional[TodoDB]:
        """
        根据 ID 获取用户的单个待办事项
        """
        return db.query(TodoDB).filter(
            TodoDB.id == todo_id, 
            TodoDB.user_id == user_id
        ).first()
    
    @staticmethod
    def create_todo(db: Session, todo_data: TodoCreate, user_id: int, assigned_by_admin: bool = False) -> TodoDB:
        """
        为用户创建新的待办事项
        
        Args:
            db: 数据库会话
            todo_data: 任务数据
            user_id: 用户ID
            assigned_by_admin: 是否由管理员分配
        """
        # 获取用户当前最大的 order 值
        max_order = db.query(TodoDB.order).filter(
            TodoDB.user_id == user_id
        ).order_by(TodoDB.order.desc()).first()
        next_order = (max_order[0] + 1) if max_order and max_order[0] is not None else 0
        
        # 创建数据库实例
        db_todo = TodoDB(
            user_id=user_id,
            title=todo_data.title,
            description=todo_data.description,
            status=todo_data.status,
            priority=todo_data.priority,
            order=getattr(todo_data, 'order', next_order),
            assigned_by_admin=assigned_by_admin
        )
        
        # 保存到数据库
        db.add(db_todo)
        db.commit()
        db.refresh(db_todo)
        
        return db_todo
    
    @staticmethod
    def update_todo(db: Session, todo_id: int, todo_data: TodoUpdate, user_id: int) -> Optional[TodoDB]:
        """
        更新用户的待办事项
        """
        db_todo = TodoService.get_todo_by_id(db, todo_id, user_id)
        if not db_todo:
            return None
        
        # 更新字段
        update_data = todo_data.dict(exclude_unset=True)
        for field, value in update_data.items():
            if hasattr(db_todo, field):
                setattr(db_todo, field, value)
        
        # 更新时间戳
        db_todo.updated_at = datetime.utcnow()
        
        # 保存更改
        db.commit()
        db.refresh(db_todo)
        
        return db_todo
    
    @staticmethod
    def delete_todo(db: Session, todo_id: int, user_id: int) -> Optional[TodoDB]:
        """
        删除用户的待办事项
        """
        db_todo = TodoService.get_todo_by_id(db, todo_id, user_id)
        if not db_todo:
            return None
        
        db.delete(db_todo)
        db.commit()
        
        return db_todo
    
    @staticmethod
    def reorder_todos(db: Session, order_updates: List[Dict[str, int]], user_id: int) -> int:
        """
        批量更新用户待办事项排序
        
        Args:
            db: 数据库会话
            order_updates: 包含 {'id': todo_id, 'order': new_order} 的列表
            user_id: 用户ID
        
        Returns:
            成功更新的记录数
        """
        updated_count = 0
        
        for update_item in order_updates:
            todo_id = update_item.get('id')
            new_order = update_item.get('order')
            
            if todo_id is None or new_order is None:
                continue
            
            db_todo = TodoService.get_todo_by_id(db, todo_id, user_id)
            if db_todo:
                db_todo.order = new_order
                db_todo.updated_at = datetime.utcnow()
                updated_count += 1
        
        db.commit()
        return updated_count
    
    @staticmethod
    def get_stats(db: Session, user_id: int) -> Dict[str, Any]:
        """
        获取用户待办事项统计信息
        """
        total = db.query(TodoDB).filter(TodoDB.user_id == user_id).count()
        
        # 按状态统计
        status_stats = {
            "pending": db.query(TodoDB).filter(
                TodoDB.user_id == user_id,
                TodoDB.status == TodoStatus.PENDING
            ).count(),
            "in_progress": db.query(TodoDB).filter(
                TodoDB.user_id == user_id,
                TodoDB.status == TodoStatus.IN_PROGRESS
            ).count(),
            "completed": db.query(TodoDB).filter(
                TodoDB.user_id == user_id,
                TodoDB.status == TodoStatus.COMPLETED
            ).count()
        }
        
        # 按优先级统计
        priority_stats = {
            "low": db.query(TodoDB).filter(
                TodoDB.user_id == user_id,
                TodoDB.priority == TodoPriority.LOW
            ).count(),
            "medium": db.query(TodoDB).filter(
                TodoDB.user_id == user_id,
                TodoDB.priority == TodoPriority.MEDIUM
            ).count(),
            "high": db.query(TodoDB).filter(
                TodoDB.user_id == user_id,
                TodoDB.priority == TodoPriority.HIGH
            ).count()
        }
        
        return {
            "total": total,
            "by_status": status_stats,
            "by_priority": priority_stats
        }
    
    @staticmethod
    def reset_todos(db: Session, sample_data: List[Dict[str, Any]]) -> int:
        """
        重置所有待办事项数据
        
        Args:
            db: 数据库会话
            sample_data: 示例数据列表
        
        Returns:
            创建的记录数
        """
        # 删除所有现有数据
        db.query(TodoDB).delete()
        
        # 插入示例数据
        created_count = 0
        for data in sample_data:
            db_todo = TodoDB(
                title=data['title'],
                description=data.get('description'),
                status=TodoStatus(data.get('status', 'pending')),
                priority=TodoPriority(data.get('priority', 'medium')),
                order=data.get('order', 0)
            )
            db.add(db_todo)
            created_count += 1
        
        db.commit()
        return created_count
    
    @staticmethod
    def reset_with_mock_data(db: Session, user_id: int) -> int:
        """
        重置用户数据库并添加mock示例数据
        
        Args:
            db: 数据库会话
            user_id: 用户ID
        
        Returns:
            创建的记录数
        """
        # 删除用户现有数据
        db.query(TodoDB).filter(TodoDB.user_id == user_id).delete()
        
        # 准备20条mock数据
        mock_data = [
            {
                "title": "学习 Python FastAPI 框架",
                "description": "深入学习 FastAPI 的核心概念和高级特性，完成待办事项 API 项目",
                "status": "in_progress",
                "priority": "high",
                "order": 0
            },
            {
                "title": "制定每日健身计划",
                "description": "设计包含有氧运动和力量训练的均衡健身方案",
                "status": "pending",
                "priority": "medium",
                "order": 1
            },
            {
                "title": "购买生活必需品",
                "description": "采购本周所需的食材、日用品和清洁用品",
                "status": "pending",
                "priority": "medium",
                "order": 2
            },
            {
                "title": "完成项目代码审查",
                "description": "审查团队成员提交的代码，提供建设性反馈和改进建议",
                "status": "pending",
                "priority": "high",
                "order": 3
            },
            {
                "title": "准备下周工作汇报",
                "description": "整理本周工作成果，准备下周一的团队汇报材料",
                "status": "pending",
                "priority": "medium",
                "order": 4
            },
            {
                "title": "学习 React 状态管理",
                "description": "深入研究 Redux、Context API 和 Zustand 等状态管理方案",
                "status": "in_progress",
                "priority": "medium",
                "order": 5
            },
            {
                "title": "整理个人文档资料",
                "description": "分类整理电脑中的工作文档、学习笔记和项目资料",
                "status": "pending",
                "priority": "low",
                "order": 6
            },
            {
                "title": "制定投资理财规划",
                "description": "研究不同投资产品，制定适合自己的理财策略",
                "status": "pending",
                "priority": "low",
                "order": 7
            },
            {
                "title": "优化代码性能",
                "description": "分析现有代码的性能瓶颈，实施优化方案提升执行效率",
                "status": "in_progress",
                "priority": "high",
                "order": 8
            },
            {
                "title": "更新个人简历",
                "description": "根据最新工作经验和技能更新简历内容，优化排版设计",
                "status": "completed",
                "priority": "medium",
                "order": 9
            },
            {
                "title": "学习 Docker 容器化技术",
                "description": "掌握 Docker 基础概念，学会构建和管理容器应用",
                "status": "pending",
                "priority": "medium",
                "order": 10
            },
            {
                "title": "设计数据库架构",
                "description": "为新项目设计高效、可扩展的数据库架构和数据模型",
                "status": "pending",
                "priority": "high",
                "order": 11
            },
            {
                "title": "准备技术分享",
                "description": "准备关于微服务架构的技术分享，制作演示文稿",
                "status": "pending",
                "priority": "medium",
                "order": 12
            },
            {
                "title": "学习英语口语",
                "description": "每天练习英语口语30分钟，提升日常交流和技术交流能力",
                "status": "in_progress",
                "priority": "medium",
                "order": 13
            },
            {
                "title": "维护开源项目",
                "description": "更新个人开源项目的文档，修复已知问题，添加新功能",
                "status": "pending",
                "priority": "low",
                "order": 14
            },
            {
                "title": "制定学习计划",
                "description": "规划下个季度的技术学习目标和时间安排",
                "status": "completed",
                "priority": "medium",
                "order": 15
            },
            {
                "title": "参加技术会议",
                "description": "参加本月的前端技术大会，学习行业最新趋势和技术",
                "status": "pending",
                "priority": "low",
                "order": 16
            },
            {
                "title": "重构旧代码模块",
                "description": "重构项目中的核心业务模块，提高代码可维护性",
                "status": "pending",
                "priority": "high",
                "order": 17
            },
            {
                "title": "建立团队规范",
                "description": "制定团队代码规范、git工作流程和代码评审标准",
                "status": "in_progress",
                "priority": "medium",
                "order": 18
            },
            {
                "title": "学习云服务部署",
                "description": "学习 AWS/阿里云等云平台的部署和运维技术",
                "status": "completed",
                "priority": "medium",
                "order": 19
            }
        ]
        
        # 插入示例数据
        created_count = 0
        for data in mock_data:
            db_todo = TodoDB(
                user_id=user_id,
                title=data['title'],
                description=data.get('description'),
                status=TodoStatus(data.get('status', 'pending')),
                priority=TodoPriority(data.get('priority', 'medium')),
                order=data.get('order', 0)
            )
            db.add(db_todo)
            created_count += 1
        
        db.commit()
        return created_count
    
    @staticmethod
    def convert_to_pydantic(db_todo: TodoDB) -> Todo:
        """
        将数据库模型转换为 Pydantic 模型
        """
        return Todo(
            id=db_todo.id,
            title=db_todo.title,
            description=db_todo.description,
            status=db_todo.status,
            priority=db_todo.priority,
            order=db_todo.order,
            assigned_by_admin=db_todo.assigned_by_admin,
            created_at=db_todo.created_at,
            updated_at=db_todo.updated_at
        )
